import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelScoreAdjustment {

    public static void main(String[] args) throws IOException {
        // 读取Excel文件
        FileInputStream file = new FileInputStream(new File("C:/Users/86134/Desktop/计算机操作系统课程-雨课堂成绩-v3.xlsx"));
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);  // 假设我们操作的是第一个工作表

        // 获取总分列（假设总分在第6列，索引从0开始）
        int totalColumnIndex = 5;

        // 从第二行开始，逐行处理
        for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);

            // 获取当前学生的总分，如果单元格为空则跳过
            Cell totalScoreCell = row.getCell(totalColumnIndex);
            if (totalScoreCell == null) {
                System.out.println("第" + rowIndex + "行总分为空，跳过该行");
                continue; // 如果总分为空，跳过该行
            }
            // 每次测试的满分（数组不做修改）
            int[] fullScores = {20, 30, 20, 30, 20}; // 每次测试的满分
            int totalScoreColumnIndex = 6; // 每次测试的列起始位置

            double totalScore = totalScoreCell.getNumericCellValue();

            // 扣除x分
            double remainingScore = 120 - totalScore;

            // 将扣分均匀地轮流分配到G-K列
            int i = 0;
            while (remainingScore > 0) {
                // 确保每个测试不超过对应的满分
                if (fullScores[i] > 0 && remainingScore > 0) {
                    fullScores[i] -= 1;  // 扣除1分
                    remainingScore -= 1; // 扣掉1分
                }
                i = (i + 1) % 5;  // 轮流扣分，循环G-K列
            }

            // 将扣完分数后的值写入到G-K列
            for (int j = 0; j < 5; j++) {
                row.getCell(6 + j).setCellValue(fullScores[j]);
            }

        }

        // 保存修改后的Excel文件
        FileOutputStream outFile = new FileOutputStream(new File("C:/Users/86134/Desktop/your_updated_excel_file.xlsx"));
        workbook.write(outFile);
        outFile.close();
        workbook.close();
        file.close();

        System.out.println("Excel file has been updated.");
    }
}
